-- init player -------------------------
-- 创建表
DROP TABLE IF EXISTS "player" CASCADE;  
CREATE TABLE IF NOT EXISTS "player" (
    "guid" UUID DEFAULT uuid_generate_v4(),
    "name" VARCHAR(60) NOT NULL UNIQUE,
    "head" VARCHAR(60) NOT NULL DEFAULT 'head_1',
    "exp" BIGINT DEFAULT '0',
    "char_list" UUID[] DEFAULT ARRAY[]::UUID[],
    PRIMARY KEY (guid)
);
-- 表属性
-- 表说明
COMMENT ON TABLE player IS '玩家信息表';
-- 字段说明
COMMENT ON COLUMN player.guid IS '玩家GUID';
COMMENT ON COLUMN player.name IS '玩家名称';
COMMENT ON COLUMN player.head IS '玩家头像';
COMMENT ON COLUMN player.exp IS '玩家经验';
COMMENT ON COLUMN player.char_list IS '玩家角色列表';
-- init player -------------------------

-- player function ---------------------------
-- 添加玩家
CREATE OR REPLACE FUNCTION "player_insert"(IN player_name VARCHAR(60))
RETURNS UUID AS $$
DECLARE
    plr_guid UUID;
BEGIN
    -- 添加player记录
    INSERT INTO player (name)
         VALUES (player_name) RETURNING guid INTO plr_guid;
    RETURN plr_guid;
END;
$$ 
LANGUAGE plpgsql;

-- 用GUID查询账号
CREATE OR REPLACE FUNCTION "player_select_by_guid"(IN player_guid UUID)
RETURNS player AS $$
DECLARE
    result player;
BEGIN
    SELECT * INTO result FROM player WHERE guid = player_guid;
    PERFORM player_merge_to_cache(result);
    RETURN result;
END;
$$ 
LANGUAGE plpgsql;

-- 用名字查询账号
CREATE OR REPLACE FUNCTION "player_select_by_name"(IN player_name name)
RETURNS player AS $$
DECLARE
    result player;
BEGIN
    SELECT * INTO result FROM player WHERE name = player_name;
    PERFORM player_merge_to_cache(result);
    RETURN result;
END;
$$ 
LANGUAGE plpgsql;

-- 合并player结果到cache
CREATE OR REPLACE FUNCTION "player_merge_to_cache"(IN tab player)
RETURNS INTEGER AS $$
DECLARE
    tmp_key_name text;
    tmp_key_guid text;
    tmp_guid text;
    tmp_char_list text;
    tmp_value text[];
BEGIN
    IF tab IS NULL THEN
        RETURN NULL;
    END IF;

    SELECT regexp_replace(''||tab.guid, '-','','g') INTO tmp_guid;
    SELECT array_to_string(tab.char_list, '|') INTO tmp_char_list;
    SELECT regexp_replace(''||tmp_char_list, '-','','g') INTO tmp_char_list;

    IF tmp_char_list = '' THEN
        tmp_char_list := '*';
    END IF;

    tmp_key_name = 'player:name_'||tab.name;
    tmp_key_guid = 'player:guid_'||tmp_guid;
    tmp_value = '{
        guid,       '||tmp_guid||',
        name,       '||tab.name||',
        head,       '||tab.head||',
        exp,        '||tab.exp||',
        char_list,  '||tmp_char_list||'
    }';

    LOOP
        UPDATE player_cache
            SET val = tmp_value
            WHERE key = tmp_key_name OR key = tmp_key_guid;
        IF found THEN
            RETURN NULL;
        END IF;

        BEGIN
            INSERT INTO player_cache(key, val) VALUES (tmp_key_name, tmp_value);
            INSERT INTO player_cache(key, val) VALUES (tmp_key_guid, tmp_value);
            RETURN NULL;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$$ 
LANGUAGE plpgsql;

-- 为player添加角色
CREATE OR REPLACE FUNCTION "player_insert_character"(IN player_guid UUID, IN char_id SMALLINT)
RETURNS characters AS $$
DECLARE
    char_guid UUID;
    result characters;
BEGIN
    SELECT characters_insert(char_id) INTO char_guid;
    UPDATE player SET char_list = array_append(char_list, char_guid) WHERE guid = player_guid;	
    SELECT * INTO result FROM characters_select_by_guid(char_guid);
    return result;
END;
$$ 
LANGUAGE plpgsql;
-- player function ---------------------------


-- player cache -------------------------
DROP FOREIGN TABLE IF EXISTS "player_cache" CASCADE;
CREATE FOREIGN TABLE "player_cache" (key text, val text[])
	SERVER redis_server
	OPTIONS (database '0', tabletype 'hash', tablekeyprefix 'player:');
-- player cache -------------------------


-- trigger function -------------------------
CREATE OR REPLACE FUNCTION "player_cache_sync"()
RETURNS TRIGGER AS $$
BEGIN
    RETURN (SELECT player_merge_to_cache(NEW));
END;
$$
LANGUAGE plpgsql;
-- trigger function -------------------------


-- trigger -------------------------
DROP TRIGGER IF EXISTS player_cache_update ON player CASCADE;
CREATE TRIGGER player_cache_update
	AFTER UPDATE ON player
	FOR EACH ROW EXECUTE PROCEDURE player_cache_sync();
-- trigger -------------------------


-- unit test -------------------------
-- INSERT INTO player (player_list) VALUES (ARRAY['a35f8d1e-9e8e-4d8e-9875-0aef1d006855']::UUID[]);
-- INSERT INTO player (player_test) VALUES ('a35f8d1e-9e8e-4d8e-9875-0aef1d006855');
-- SELECT * FROM player
-- update uuid_arr update set list = (select list from uuid_arr where guid='a35f8d1e-9e8e-4d8e-9875-0aef1d006855')::uuid[] || ARRAY['a35f8d1e-9e8e-4d8e-9875-0aef1d006855']::uuid[];
-- select (select list from uuid_arr where guid='a35f8d1e-9e8e-4d8e-9875-0aef1d006855')::uuid[] || 'a35f8d1e-9e8e-4d8e-9875-0aef1d006855';
-- unit test -------------------------